package com.huayue.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.RoomStatus;
import com.huayue.util.DateHelper;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 房间状态
 */
@Component
public class RoomStatusDao implements BaseDao<RoomStatus> {
    @Override
    public int insert(RoomStatus entity) {
        return DBHelper.executeUpdate("INSERT INTO room_status (room_id, status_id, is_deleted ) VALUES (? , ? ,?)",
            entity.getRoomId(), entity.getStatusId(), 0);
    }

    @Override
    public int update(RoomStatus entity) {
        return DBHelper.executeUpdate("update room_status set status_id=? where id=?", entity.getStatusId(),
            entity.getId());
    }

    @Override
    public int delete(RoomStatus entity) {
        return DBHelper.executeUpdate("update room_status set is_deleted=1 where id=?", entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper.executeQuery(
            "select r.name rname,rst.name rstname,rs.gmt_create rs_gmtcreate,rs.gmt_modified rs_gmtmodified, "
                + "rs.is_deleted rs_isdeleted from room r INNER JOIN room_status rs on rs.room_id=r.id "
                + "INNER JOIN room_status_type rst on rst.id=rs.status_id where 1=1 and rs.is_deleted=0");
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        RoomStatus roomStatus = (RoomStatus)obj[0];
        String sql = "select rs.id id, r.name rname,rst.name rstname,rs.gmt_create rs_gmtcreate,\n"
            + "rs.gmt_modified rs_gmtmodified, c.realname cname,rst.id rstid from room r \n"
            + "left JOIN room_status rs on rs.room_id=r.id  left join customer c on rs.customer_id=c.id\n"
            + "INNER JOIN room_status_type rst on rst.id=rs.status_id where 1=1 and rs.is_deleted=0 ";
        if (obj[0] != null && !"".equals(roomStatus.getName())) {
            sql += "and concat(rst.name,r.name) like  '%" + roomStatus.getName() + "%' ";
        }
        sql += " order by rs.gmt_modified desc limit ?,? ";
        return DBHelper.executeQuery(sql, page.getBegin(), page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(RoomStatus entity) {
        return DBHelper.executeQuery("select * from room  where is_deleted=0 and id=? ", entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        RoomStatus roomStatus = (RoomStatus)obj[0];
        String sql = "select count(*) counts from room r \n"
            + "left JOIN room_status rs on rs.room_id=r.id  left join customer c on rs.customer_id=c.id\n"
            + "INNER JOIN room_status_type rst on rst.id=rs.status_id where 1=1 and rs.is_deleted=0 ";
        if (obj[0] != null && !"".equals(roomStatus.getName())) {
            sql += "and concat(rst.name,r.name) like  '%" + roomStatus.getName() + "%' ";
        }
        sql += " order by rs.gmt_modified desc ";
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());

    }

    public List<Map<String, Object>> findRoom(Object... obj) {
        return DBHelper.executeQuery("select * from room");
    }

    /**
     * 查询每种房间余量
     * 
     * @param checkDate
     * @return
     */
    public List<Map<String, Object>> findAllRoomStatusType(String checkDate) {
        if ("".equals(checkDate)) {
            checkDate = DateHelper.DateToStringShort(new Date());
        }
        String sql = "select id,name,counts,color from room_status_type rst left join  (\n"
            + "select rs.status_id,count(rs.status_id) counts from room_status rs\n"
            + "left join room r on rs.room_id=r.id left join room_status_type rst on rst.id=rs.status_id\n"
            + "left join room_status_type rsts on rsts.id=rs.status_id\n"
            + "where rs.is_deleted = 0 and r.is_deleted=0 and ('" + checkDate + "'>=rs.in_time and '" + checkDate
            + "'<=rs.out_time) or (rst.id =1 or rst.id=4 or rst.id=5)  group by rst.id ) s1 on rst.id = s1.status_id\n"
            + "where rst.is_deleted = 0";
        List<Map<String, Object>> finalList = DBHelper.executeQuery(sql);
        // 查当天预定房间但时间已超出的房间数量加给总房间数
        String sql2 = "select rs.status_id,count(rs.status_id) counts from room_status rs\n"
            + "left join room r on rs.room_id=r.id left join room_status_type rst on rst.id=rs.status_id\n"
            + "left join room_status_type rsts on rsts.id=rs.status_id where rs.is_deleted = 0 \n"
            + "and r.is_deleted=0  and rs.status_id=3 and ('" + checkDate + "'<rs.in_time or '" + checkDate
            + "'>rs.out_time) group by rst.id ";
        List<Map<String, Object>> list = DBHelper.executeQuery(sql2);
        if (list.size() != 0) {
            for (Map<String, Object> map : finalList) {
                if ("1".equals(map.get("id").toString())) {
                    if (map.get("counts") == null) {
                        map.put("counts", 0);
                    }
                    Integer old = Integer.valueOf(map.get("counts").toString());
                    Integer newn = Integer.valueOf(list.get(0).get("counts").toString());
                    old = old + newn;
                    map.put("counts", old);
                }
            }
        }
        // 查询预定单中的当天已预定但不在房态表中显示的房间
        String sqlpredict = "select rs.room_id,c.realname from room_predict_order rpo\n"
            + "RIGHT JOIN room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
            + "inner join room_status rs on rs.room_id = rpod.room_id left join customer c on c.id=rpo.customer_id "
            + "where rpod.is_status=0 and rs.in_time!=rpo.predict_in_time and ('" + checkDate
            + "'>=rpo.predict_in_time and '" + checkDate + "'<=rpo.predict_out_time) ";
        List<Map<String, Object>> listpre = DBHelper.executeQuery(sqlpredict);
        if (listpre.size() == 0) {
            return finalList;
        }
        for (Map<String, Object> map : finalList) {
            if ("预定".equals(map.get("name").toString())) {
                Integer count = 0;
                if (map.get("counts") != null) {
                    count = Integer.valueOf(map.get("counts").toString());
                }
                count += listpre.size();
                map.put("counts", count);
            }
        }
        return finalList;
    }

    // 查询的房间和楼层
    public List<Map<String, Object>> findAllRoomStatus(String checkDate) {
        if ("".equals(checkDate)) {
            checkDate = DateHelper.DateToStringShort(new Date());
        }
        String noYuDingZaiZhu =
            "select r.id rid,rt.id rtid,rt.name rtname,rt.floors rtfloors,r.name rname,rst.id rstid,rst.name rstname,rst.color from room_type rt\n"
                + "left join room r on rt.id = r.room_type_id left join room_status rs on rs.room_id=r.id\n"
                + "left join room_status_type rst on rs.status_id=rst.id \n"
                + "left join customer c on rs.customer_id=c.id  where r.is_deleted=0\n"
                + "and (rst.id = 1 or rst.id = 4 or rst.id=5) or ('" + checkDate + "'<rs.in_time or '" + checkDate
                + "'>rs.out_time)";
        List<Map<String, Object>> finalList = DBHelper.executeQuery(noYuDingZaiZhu);
        // 将查询到的所有空房状态都改为空房
        for (Map<String, Object> map : finalList) {
            if ("预定".equals(map.get("rstname").toString()) || "在住".equals(map.get("rstname").toString())) {
                map.put("rstid", 1);
                map.put("rstname", "空房");
                map.put("color", "#50e199");
                map.remove("realname");
            }
        }
        // 查询当天预定/在住
        String YuDingZaiZhu =
            "select r.id rid,rt.id rtid,rt.name rtname,rt.floors rtfloors,r.name rname,rst.id rstid,rst.name rstname,rst.color,c.realname,rs.quit from room_type rt\n"
                + "left join room r on rt.id = r.room_type_id  left join room_status rs on rs.room_id=r.id\n"
                + "left join room_status_type rst on rs.status_id=rst.id \n"
                + "left join customer c on rs.customer_id=c.id where r.is_deleted=0 \n"
                + "and (rst.id = 2 or rst.id=3) and ('" + checkDate + "'>=rs.in_time and '" + checkDate
                + "'<=rs.out_time)";
        finalList.addAll(DBHelper.executeQuery(YuDingZaiZhu));
        // 查询预定单中的当天已预定的房间
        String sqlpredict = "select rs.room_id,c.realname from room_predict_order rpo\n"
            + "RIGHT JOIN room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
            + "inner join room_status rs on rs.room_id = rpod.room_id left join customer c on c.id=rpo.customer_id where rpo.is_status=0 and rpod.is_status=0 and ('"
            + checkDate + "'>=rpo.predict_in_time and '" + checkDate + "'<=rpo.predict_out_time) ";
        List<Map<String, Object>> listpre = DBHelper.executeQuery(sqlpredict);
        if (listpre.size() == 0) {
            return finalList;
        }
        for (Map<String, Object> map : listpre) {
            for (Map<String, Object> map2 : finalList) {
                if (map2.get("rstid").toString().equals("1")
                    && map.get("room_id").toString().equals(map2.get("rid").toString())) {
                    map2.put("rstid", 3);
                    map2.put("rstname", "预定");
                    map2.put("color", "#29e2e7");
                    map2.put("realname", map.get("realname").toString());
                }
            }
        }
        return finalList;
    }

    public List<Map<String, Object>> findCount(Object... obj) {
        String sql = "select rt.name rtName,count(*) counts from  room_status rs \n"
            + "INNER JOIN room_status_type rst on rs.status_id=rst.id \n" + "INNER JOIN room r on rs.room_id = r.id \n"
            + "INNER JOIN room_type rt on r.room_type_id=rt.id GROUP BY rt.name ";
        // if()
        return null;
    }

    /**
     * 查询所有房态类型
     * 
     * @return
     */
    public List<Map<String, Object>> findAllStatusTypeInfo() {
        return DBHelper.executeQuery("select * from room_status_type");
    }

    /**
     * 根据房间id查找房间房态
     * 
     * @param roomId
     * @return 房态id，房间号，房态id
     */
    public Map<String, Object> findStatusByRoomId(String roomId) {
        String sql = "select rs.id,rs.status_id,r.name from room_status rs "
            + " left join room r on r.id=rs.room_id where r.id=?";
        return DBHelper.executeQuery(sql, roomId).get(0);
    }

    /**
     * 将最新的顾客给最新修改的房间状态
     * 
     * @return
     */
    public int updateCustomerByNewTime() {
        String sql =
            "update room_status set customer_id=(select max(id) from customer) where id order by gmt_modified desc limit 1";
        return DBHelper.executeUpdate(sql);
    }

    /**
     * 查询已入住房间
     * 
     * @return
     */
    public List<Map<String, Object>> findStatusCheckIn() {
        String sql = "select r.id rid,r.name rname from room r inner join room_status rs on rs.room_id=r.id\n"
            + "where rs.status_id=2 order by rs.gmt_modified desc";
        return DBHelper.executeQuery(sql);
    }

    /**
     * 将最新的顾客信息给房间状态
     * 
     * @param roomId
     * @return
     */
    public int updateCustomerByRoomId(String roomId) {
        return DBHelper
            .executeUpdate("update room_status set customer_id=(select max(id) from customer) where room_id=?", roomId);
    }

    /**
     * 根据房间ID修改房间状态
     * 
     * @param entity
     * @return
     */
    public int updateStatusByRoomId(RoomStatus entity) {
        return DBHelper.executeUpdate(
            "update room_status set status_id=?,in_time=null,out_time=null,customer_id=null,quit=0 where room_id=?",
            entity.getStatusId(), entity.getRoomId());
    }

    /**
     * 修改房间为待退房
     * 
     * @return
     */
    public int updateQuitRoomAUTO() {
        String sql =
            "update room_status set quit=1 where out_time<now() and out_time >(select subdate(now(),interval 3 minute))";
        return DBHelper.executeUpdate(sql);
    }

    /**
     * 执行将今日待退房间更改为待打扫
     * 
     * @return
     */
    public int updateForceQuitAUTO() {
        // 将押金单的is_return 改为9
        String sqldep = "select room_id from room_status where quit=1";
        List<Map<String, Object>> list = DBHelper.executeQuery(sqldep);
        String sqldepUpdate =
            "update room_deposit set is_return=9 where id=(select d.id from (select id from room_deposit rs where rs.room_id = ? ORDER BY id desc limit 1) d)";
        String sqldetailsUpdate =
            "update room_real_order_details set is_status=9 where id=(select rs.id from (select id from room_real_order_details where room_id=? order by id desc limit 0,1)rs)";
        // 修改订单详情状态为已退房
        for (Map<String, Object> map : list) {
            DBHelper.executeUpdate(sqldetailsUpdate, map.get("room_id").toString());
        }
        // 修改房态
        String sql =
            "update room_status set quit=0,customer_id=null,status_id=4,in_time=null,out_time=null where quit=1";
        return DBHelper.executeUpdate(sql);
    }

    /**
     * 给房间加?天
     * 
     * @return
     */
    public int addDaysByRoom(Integer days) {
        if (days == 0 || days == null) {
            return 0;
        }
        String sql = "update room_status set out_time=date_add(out_time,interval " + days + " day);";
        return DBHelper.executeUpdate(sql);
    }
}
